package com.esaheki.flashcards.dao;

import java.sql.*;
import java.util.ArrayList;

import com.esaheki.flashcards.classes.*;

public class CardDAO extends Conexao {

	public CardDAO() throws ClassNotFoundException, SQLException {
		super();
	}
	
	public void insertCard(Card card, Category cat) throws SQLException {
		String sql = "INSERT INTO cards VALUES (NULL, ?, ?, ?, ?, ?)";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, card.getFront());
		stmt.setString(2, card.getBack());
		stmt.setInt(3, cat.getId());
		stmt.setInt(4, 0);
		stmt.setInt(5, 0);
		stmt.executeUpdate();
	}
	
	public Card getCompleteCard(Card card) throws SQLException {
		String sql = "SELECT * FROM cards WHERE front = ? AND back = ?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, card.getFront());
		stmt.setString(2, card.getBack());
		System.out.println(stmt);
		ResultSet rs = stmt.executeQuery();
		rs.last();
		card.setId(rs.getInt("id"));
		card.setCountRight(rs.getInt("correct"));
		card.setCountTests(rs.getInt("total"));
		return card;
	}
	
	public void updateCard(Card card) throws SQLException {
		String sql = "UPDATE cards SET front = ?, back = ?, correct = ?, total = ? WHERE id = ?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, card.getFront());
		stmt.setString(2, card.getBack());
		stmt.setInt(3, card.getCountRight());
		stmt.setInt(4, card.getCountTests());
		stmt.setInt(5, card.getId());
		stmt.executeUpdate();
	}
	
	public void deleteCard(Card card) throws SQLException {
		String sql = "DELETE FROM cards WHERE id = ?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, card.getId());
		stmt.executeUpdate();
	}
	
	public ArrayList<Card> getCardListByCategory(Category cat) throws SQLException {
		ArrayList<Card> list = new ArrayList<Card>();
		
		String sql = "SELECT * FROM cards WHERE category = ? ORDER BY id";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, cat.getId());
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			Card card = new Card();
			card.setId(rs.getInt("id"));
			card.setFront(rs.getString("front"));
			card.setBack(rs.getString("back"));
			card.setCountRight(rs.getInt("correct"));
			card.setCountTests(rs.getInt("total"));
			list.add(card);
		}

		return list;
	}
	
	public ArrayList<Card> getCardListByCategoryId(int id) throws SQLException {
		ArrayList<Card> list = new ArrayList<Card>();
		
		String sql = "SELECT * FROM cards WHERE category = ? ORDER BY id";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			Card card = new Card();
			card.setId(rs.getInt("id"));
			card.setFront(rs.getString("front"));
			card.setBack(rs.getString("back"));
			card.setCountRight(rs.getInt("correct"));
			card.setCountTests(rs.getInt("total"));
			list.add(card);
		}

		return list;
	}
	
	public ArrayList<Card> getCardListByUser(User user) throws SQLException {
		ArrayList<Card> list = new ArrayList<Card>();
		
		String sql = "SELECT cards.* FROM cards, categories WHERE categories.user = ? AND cards.category = categories.id ORDER BY category, id";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, user.getId());
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			Card card = new Card();
			card.setId(rs.getInt("id"));
			card.setFront(rs.getString("front"));
			card.setBack(rs.getString("back"));
			card.setCountRight(rs.getInt("correct"));
			card.setCountTests(rs.getInt("total"));
			list.add(card);
		}

		return list;
	}
	
	public ArrayList<Card> getCardListByUserId(int id) throws SQLException {
		ArrayList<Card> list = new ArrayList<Card>();
		
		String sql = "SELECT cards.* FROM cards, categories WHERE categories.user = ? AND cards.category = categories.id ORDER BY category, id";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			Card card = new Card();
			card.setId(rs.getInt("id"));
			card.setFront(rs.getString("front"));
			card.setBack(rs.getString("back"));
			card.setCountRight(rs.getInt("correct"));
			card.setCountTests(rs.getInt("total"));
			list.add(card);
		}

		return list;
	}
	
	public ArrayList<Card> getAllCardList() throws SQLException {
		ArrayList<Card> list = new ArrayList<Card>();
		
		String sql = "SELECT * FROM cards ORDER BY category, id";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			Card card = new Card();
			card.setId(rs.getInt("id"));
			card.setFront(rs.getString("front"));
			card.setBack(rs.getString("back"));
			card.setCountRight(rs.getInt("correct"));
			card.setCountTests(rs.getInt("total"));
			list.add(card);
		}

		return list;
	}
	
	public Card getCardById(int id) throws SQLException {
		Card card = new Card();
		String sql = "SELECT * FROM cards WHERE id = ?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		rs.last();
		
		card.setId(rs.getInt("id"));
		card.setFront(rs.getString("front"));
		card.setBack(rs.getString("back"));
		card.setCountRight(rs.getInt("correct"));
		card.setCountTests(rs.getInt("total"));
		return card;
	}
}
